package com.omesoft.hksnore.util.dbhelper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.omesoft.hksnore.util.data.MyDateUtil;
import com.omesoft.hksnore.util.entity.Family;
import com.omesoft.hksnore.util.entity.Medix_Pub_Sync_Sleep;
import com.omesoft.hksnore.util.entity.Medix_Pub_Sync_SleepDetail;
import com.omesoft.hksnore.util.entity.TemSleepDTO;
import com.omesoft.hksnore.util.snore.SnoreEntity;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Omesoft on 2017/3/13.
 */

public class DBHelper4Sleep extends SQLiteOpenHelper {
    private static String DB_PATH = SetData.DB_PATH;// 设置数据库的路径
    private static DBHelper4Sleep dbHelper;
    private static SQLiteDatabase db;
    private final static String TAG = "DBHelper4Sleep";
    private StringBuilder sb;

    // private StringBuilder sb;
    private DBHelper4Sleep(Context context, int versionCode, String DB_NAME) {
        super(context, DB_NAME, null, versionCode);
        createDataBase(this, context, DB_NAME);// 创建数据库

    }

    /** 采用双重检查加锁实例化单件 */
    public static DBHelper4Sleep getInstance(Context context, String DB_NAME) {
        // 双重加锁！！

        if (dbHelper == null) // 第一次检查
        {
            synchronized (DBHelper4Sleep.class) {
                if (dbHelper == null) // 第二次检查
                {
                    int versionCode = SoftParam.getVersionCode(context);
                    dbHelper = new DBHelper4Sleep(context, versionCode, DB_NAME);
                }
            }
        }
        return dbHelper;
    }

    private static void copydatabase(Context context, String dbName) {
        try {
            // 在src中得到预先保存的数据库,并于流的形式返回
            InputStream myInput = context.getAssets().open(dbName);
            String outFileName = DB_PATH + dbName;
            // 用流的形式把数据库文件添加到指定的目录里
            OutputStream myOutput = new FileOutputStream(outFileName);
            byte[] buffer = new byte[2048];
            int length;
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);// 复制
            }
            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch (IOException e) {
            // Log.e("test", e.getMessage());
        }
    }

    private static void createDataBase(DBHelper4Sleep dbHelper, Context context, String DB_NAME) {
        boolean dbExist = checkDataBase(DB_NAME);
        if (!dbExist) {// 判断手机中是否存在数据库
            copydatabase(context, DB_NAME);// 如果不存在数据库就把数据库复制到指定的路径
//            db = dbHelper.getReadableDatabase();
            db = dbHelper.getWritableDatabase();
            if (db != null) {
                createDataBase(dbHelper, context, DB_NAME);
            }
        } else{
            if (db != null){
                db.close();
//                db = dbHelper.getReadableDatabase();
                db = dbHelper.getWritableDatabase();
            }
        }

    }

    private static boolean checkDataBase(String DB_NAME) {
        File dir = new File(DB_PATH);
        Log.v("DBHelper::", "dir.mkdir()" + dir+"    "+"DB_NAME   "+DB_NAME);
        if (!dir.exists()) {

            dir.mkdir(); // 如果目录中不存在，创建这个目录
        } else {
            try {
                String myPath = DB_PATH + DB_NAME;
                // 检查数据库是否存在
                Log.v("DBHelper::checkDataBase", "myPath=" + myPath);
                db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
            } catch (Exception e) {
              //  dir.mkdirs(); // 如果目录中不存在，创建这个目录
                e.printStackTrace();
            }
        }
        return db != null ? true : false;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        creatNewTable(db, 0);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.e("DBHelper4Sleep", "onUpgrade::oldVersion::" + oldVersion);
        Log.e("DBHelper4Sleep", "onUpgrade::newVersion::" + newVersion);
        // 这个数据库是以软件的版本号作为自己的版本号
        StringBuilder builder = new StringBuilder();
        if (oldVersion <= 464) {
            try {
                // 对Medix_Pub_Sync_Sleep增加一列qqHealth，表示是否已同步到QQ健康
                // 用0表示false，1表示true
                builder.setLength(0);
                builder.append("ALTER TABLE Medix_Pub_Sync_Sleep2 ADD COLUMN qqHealth INT");
                db.execSQL(builder.toString());
                builder.setLength(0);
                builder.append("UPDATE Medix_Pub_Sync_Sleep2 SET qqHealth = 0");
                db.execSQL(builder.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // 鼾声的数据表
        if (oldVersion <= 469) {
            Log.e("DBHelper4Sleep", "3");
            try {
                Log.e("DBHelper4Sleep", "SleepSnore not exist");
                builder.setLength(0);
                builder.append("CREATE TABLE SleepSnore (");
                builder.append("ID INTEGER,");
                builder.append("SleepID TEXT,");
                builder.append("SnoreID TEXT,");
                builder.append("MemberID INTEGER,");
                builder.append("Decibels TEXT,");
                builder.append("SampleCount INTEGER,");
                builder.append("SnoreCount INTEGER,");
                builder.append("RecordDate TEXT,");
                builder.append("CreatedDate TEXT,");
                builder.append("UpdatedDate TEXT,");
                builder.append("isDeleted INTEGER,");
                builder.append("PRIMARY KEY (SnoreID))");
                db.execSQL(builder.toString());
                Log.e("DBHelper4Sleep", "SleepSnore is create");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (oldVersion < 477) {
            try {
                builder.setLength(0);
                builder.append("ALTER TABLE Medix_Pub_Sync_Sleep2 ADD COLUMN DeviceName TEXT");
                db.execSQL(builder.toString());
                builder.setLength(0);
                builder.append("ALTER TABLE Medix_Pub_Sync_Sleep2 ADD COLUMN SourceType INTEGER");
                db.execSQL(builder.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (oldVersion <= 486) {
            try {
                builder.setLength(0);
                builder.append("ALTER TABLE Medix_Pub_Sync_Sleep2 ADD COLUMN SnoreTime INT");
                db.execSQL(builder.toString());
                builder.setLength(0);
                builder.append("ALTER TABLE Medix_Pub_Sync_Sleep2 ADD COLUMN SnoreCount INT");
                db.execSQL(builder.toString());
                builder.setLength(0);
                builder.append("ALTER TABLE Medix_Pub_Sync_Sleep2 ADD COLUMN BreathRate INT");
                db.execSQL(builder.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Create table
     */
    public void creatNewTable(SQLiteDatabase db, int flag) {
        // 0,如果数据库没有Medix_Pub_Sync_Sleep表就创建
        String sql = "";
        switch (flag) {
            case 0:
                sql = "create table download_info(_id integer PRIMARY KEY AUTOINCREMENT, thread_id integer, "
                        + "start_pos integer, end_pos integer, compelete_size integer,url char)";
                break;
            // case 1:
            // sql =
            // "CREATE TABLE Medix_Pub_Sync_Sleep(ID integer NOT NULL PRIMARY KEY AUTOINCREMENT , SleepID varchar(50) NOT NULL, "
            // +
            // "MemberID integer NOT NULL, InBedTime integer NOT NULL, SleepTime integer NOT NULL, DeepSleepTime integer NOT NULL,"
            // +
            // " SleepScore integer NOT NULL, Remark varchar(50) NOT NULL, WakeupState integer NOT NULL,StartDate date NOT NULL,EndDate date NOT NULL,"
            // +
            // "CreatedDate date NOT NULL,UpdatedDate date NOT NULL,IsDeleted integer NOT NULL)";
            // break;
            // case 2:
            // sql =
            // "CREATE TABLE Medix_Pub_Sync_SleepDetail(ID integer NOT NULL PRIMARY KEY AUTOINCREMENT ,MemberID integer NOT NULL,"
            // +
            // "SleepID varchar(50) NOT NULL, SleepDetailID varchar(50) NOT NULL, SleepQuality float NOT NULL, SampleCount integer NOT NULL,"
            // + " ActCount integer NOT NULL, TurnCount integer NOT NULL,"
            // +
            // "CreatedDate date NOT NULL,UpdatedDate date NOT NULL,IsDeleted integer NOT NULL)";
            // break;
            default:
                break;
        }
        // Log.v(TAG, "sql=" + sql);
        db.execSQL(sql);
    }

    public Cursor findAllByTime(String table, int FamilyID, String time) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append(" where FamilyID=").append(FamilyID).append(" and UpdatedDate >'").append(time).append("'");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findAllByTime::" + sb.toString());
        return cursor;
    }

    public Cursor findAll(String table) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    public Cursor findAllAudio(String table, int mixId) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" where mix_id=").append(mixId);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    public Cursor find(String table, String[] keys) {// 查找数据
        try {
            return db.query(table, keys, null, null, null, null, null);
        } catch (Exception e) {
            return null;
        }
    }

    public Cursor find(String table, String[] keys, String key, Object value) {
        try {
            return db.query(table, keys, key + "='" + value.toString() + "'", null, null, null, null);
        } catch (Exception e) {
            // Log.v("test", e.toString());
            return null;
        }
    }

    public Cursor findAllByMix(String table, String type, int value) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append(" where type!=").append(value);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findAllByMix::" + sb.toString());
        return cursor;
    }

    public long InsertOrUpdateSave1(String table, Integer saveid, String name, String icon) {// 插入或更新数据
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("icon", " ");
        try {
            if (saveid != null) {
                return db.update(table, values, "id=" + saveid, null);
            } else {
                return db.insert(table, null, values);
            }
        } catch (Exception e) {
            Log.e("插入或更新出错", e.toString());
            return -1;
        }
    }

    public boolean InsertOrUpdateMixSave(String table, Integer saveid, int returnId, String audioid, float volume) {// 插入或更新数据
        ContentValues values = new ContentValues();
        values.put("mix_id", returnId);
        values.put("audio_id", audioid);
        values.put("volume", volume);
        try {
            if (saveid != null) {
                return db.update(table, values, "id=" + saveid, null) > 0;
            } else {
                return db.insert(table, null, values) > 0;
            }
        } catch (Exception e) {
            Log.e("插入或更新出错", e.toString());
            return false;
        }
    }

    public boolean delete(String table, Integer rowId) {// 删除数据
        try {
            return db.delete(table, "id=" + rowId, null) > 0;

        } catch (Exception e) {
            Log.e("删除出错", e.toString());
            return false;
        }

    }

    public boolean deleteMixAudio(String table, Integer rowId) {// 删除数据
        try {
            return db.delete(table, "mix_id=" + rowId, null) > 0;
        } catch (Exception e) {
            // Log.e("删除出错", e.toString());
            return false;
        }

    }

    public void insertRemark(String table, int id, String Name) {
        sb = new StringBuilder();
        sb.append("INSERT INTO ").append(table).append(" VALUES(").append(id).append(",'").append(Name).append("')");
        // Log.v(TAG, "insertRemark::" + sb.toString());
        db.execSQL(sb.toString());
    }

    public void changeRemarkItem(String table, ArrayList<String> Names) {
        db.beginTransaction(); // 手动设置开始事务
        try {
            // 批量处理操作
            sb = new StringBuilder();
            sb.append("DELETE from ").append(table);
            db.execSQL(sb.toString());
            VACUUM();
            int size = Names.size();
            for (int i = 0; i < size; i++) {
                sb = new StringBuilder();
                sb.append("INSERT INTO ").append(table).append(" VALUES(").append(i).append(",'").append(Names.get(i)).append("')");
                // Log.v(TAG, "changeRemarkItem::" + sb.toString());
                db.execSQL(sb.toString());
            }
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交。
            // 在setTransactionSuccessful和endTransaction之间不进行任何数据库操作
        } catch (Exception e) {
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    public void deleteRemark(String table, String id) {
        sb = new StringBuilder();
        sb.append("DELETE  from ").append(table).append(" WHERE Name = '").append(id).append("'");
        // Log.v(TAG, "deleteRemark::" + sb.toString());
        db.execSQL(sb.toString());
        VACUUM();
    }
    public void deleteBySleepID(String table, String SleepID) {
        sb = new StringBuilder();
        sb.append("DELETE  from ").append(table).append(" WHERE SleepID = '").append(SleepID).append("'");
        // Log.v(TAG, "deleteRemark::" + sb.toString());
        db.execSQL(sb.toString());
        VACUUM();
    }

    public Cursor checkRemark(String table, String name) {

        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" WHERE Name = '").append(name).append("'");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "checkRemark::" + sb.toString());
        return cursor;
    }

    public Cursor findBigIDRemark(String table) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" ORDER BY id DESC LIMIT 1");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findBigIDRemark::" + sb.toString());
        return cursor;
    }

    /**
     * 同步：插入或更新或删除表
     *
     * @param familys
     */
    public void inserOrUpdatetFamily(List<Family> familys, List<Family> familysold) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        db.beginTransaction(); // 手动设置开始事务
        try {
            // 批量处理操作
            boolean isEx = false;
            for (Family familyold : familysold) {
                isEx = false;
                for (Family family : familys) {
                    if (familyold.getFamilyId() == family.getFamilyId()) {
                        family.setWaistline(familyold.getWaistline());
                        family.setHipline(familyold.getHipline());
                        isEx = true;
                    }
                }
                if (!isEx) {
                    String sql = "delete from " + SetData.TABLE_NAME_MEMBER_FAMILY + " where FamilyID=" + familyold.getFamilyId();
                    db.execSQL(sql);
                    VACUUM();
                }
            }
            for (Family family : familys) {
                String sql = "insert or replace into " + SetData.TABLE_NAME_MEMBER_FAMILY
                        + "(_id,FamilyID,MemberID,Name,Gender,Birthday,Weight,Height,Avatar,Waistline,Hipline,CreatedDate,UpdatedDate,Phone) values( null,"
                        + family.getFamilyId() + "," + family.getMemberId() + ",'" + family.getName() + "','" + family.getRealName() + "',"
                        + family.getGender() + ",'" + family.getBirthday() + "'," + family.getWeight() + "," + family.getHeight() + ",'" + family.getAvatar()
                        + "' ," + family.getWaistline() + "," + family.getHipline() + " , '" + recordDate + "','" + recordDate + "','" + family.getPhone()
                        + "')";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    /**
     * 根据字段查询该行,倒序
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findByKey(String table, String key, int value) {
        String sql = "select * from " + table + " where " + key + "=" + value + " order by RecordDate desc ";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor == null) {
            return null;
        } else {
            return cursor;
        }

    }

    /**
     * 根据字段查询该行,倒序
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findByKeyF(String table, String key, int value) {
        String sql = "select * from " + table + " where " + key + "=" + value;
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor == null) {
            return null;
        } else {
            return cursor;
        }

    }

    public Cursor findByKeyDF(String table, String key, int value) {
        String sql = "select * from " + table + " where " + key + "=" + value + " and IsDeleted = '0'" + " order by RecordDate desc ";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor == null) {
            return null;
        } else {
            return cursor;
        }

    }

    /**
     * 根据字段查询该行,倒序
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findByKey(String table, String[] key, int[] value) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from " + table + " where ");
        for (int i = 0; i < key.length; i++) {
            sql.append(key + "=" + value);
            if (i < key.length - 1) {
                sql.append(" and ");
            }
        }
        sql.append(" order by _id desc");
        Cursor cursor = db.rawQuery(sql.toString(), null);
        if (cursor == null) {
            return null;
        } else {
            return cursor;
        }

    }

    /**
     * 根据字段查询该行
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findByKeyF(String table, String[] key, int[] value) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from " + table + " where ");
        for (int i = 0; i < key.length; i++) {
            sql.append(key[i] + "=" + value[i]);
            if (i < key.length - 1) {
                sql.append(" and ");
            }
        }
        Cursor cursor = db.rawQuery(sql.toString(), null);
        if (cursor == null) {
            return null;
        } else {
            return cursor;
        }

    }

    /**
     * 根据字段查询该行,倒序
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findByKey(String table, String key, String value) {
        String sql = "select * from " + table + " where " + key + "=" + value + " order by _id desc ";
        System.out.println(sql);
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    public Cursor findByKeyString(String table, String key, String value) {
        String sql = "select * from " + table + " where " + key + "='" + value + "'  order by ID asc ";
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    /**
     * 根据字段查询该行,倒序
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findByKeyDesc(String table, String key, String value) {
        String sql = "select * from " + table + " where " + key + "=" + value + " order by RecordDate desc ";
        System.out.println(sql);
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;

    }

    /**
     *
     */
    public void update(String table, String[] keys, String[] values, int[] values1, float[] values2, String Dkey, int Dvalue) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sql = new StringBuilder();
        sql.append(" update " + table + " set ");
        for (int i = 0; i < keys.length; i++) {
            sql.append(keys[i]);
            if (i < values.length) {
                sql.append(" = '" + values[i] + "'");
            } else if (i >= values.length && i < values.length + values1.length) {
                sql.append(" = " + values1[i - values.length]);
            } else {
                sql.append(" = " + values2[i - values.length - values1.length]);
            }
            if (i != keys.length - 1) {
                sql.append(" , ");
            }
        }
        sql.append(" where " + Dkey + " = " + Dvalue);
        db.execSQL(sql.toString());
    }

    public void updateTime(String table, String[] keys, String[] values, int[] values1, float[] values2, String Dkey, int Dvalue) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sql = new StringBuilder();
        sql.append(" update " + table + " set ");
        for (int i = 0; i < keys.length; i++) {
            sql.append(keys[i]);
            if (i < values.length) {
                sql.append(" = '" + values[i] + "'");
            } else if (i >= values.length && i < values.length + values1.length) {
                sql.append(" = " + values1[i - values.length]);
            } else {
                sql.append(" = " + values2[i - values.length - values1.length]);
            }
            if (i != keys.length - 1) {
                sql.append(" , ");
            }
        }
        sql.append(", Timestamp = '" + recordDate + "' where " + Dkey + " = " + Dvalue);
        db.execSQL(sql.toString());
    }

    /**
     * 根据字段删除改行
     *
     * @param table
     * @param key
     * @param value
     */
    public void deleteByKey(String table, String key, int value) {
        String sql = "delete from " + table + " where " + key + "=" + value;
        db.execSQL(sql);
        VACUUM();
    }

    /**
     * 根据字段删除改行
     *
     * @param table
     * @param key
     * @param value
     */
    public void deleteByKey(String table, String key, String value) {
        String sql = "delete from " + table + " where " + key + "=" + value;
        db.execSQL(sql);
        VACUUM();

    }

    public static SQLiteDatabase getDb() {
        return db;
    }

    public static void setDb(SQLiteDatabase db) {
        DBHelper4Sleep.db = db;
    }

    /**
     * 查找某一KEY的所有
     */
    public Cursor find2Key(String table, String[] keys) {
        StringBuilder sql = new StringBuilder();
        sql.append("select ");
        for (int i = 0; i < keys.length; i++) {
            sql.append(keys[i]);
            if (i != keys.length - 1) {
                sql.append(",");
            }
        }
        sql.append(" from " + table);
        Cursor cursor = db.rawQuery(sql.toString(), null);
        return cursor;
    }

    /**
     * 查找某一KEY的所有
     */
    public Cursor find2KeyByMaxIDAndKey(String table, String key, int value) {
        String sql = "select top 1 from " + table + " where " + key + "=" + value + " order by _id desc ";
        // System.out.println("sql ="+sql);
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    /**
     * 查找某一KEY的所有
     */
    public Cursor find2KeyByMaxIDAndKeyDF(String table, String key, int value) {
        String sql = "select * from " + table + " where " + key + " = " + value + " and IsDeleted = " + 0 + " order by _id desc  limit 0,1 ";
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    /**
     * 根据字段查询该行,倒序
     *
     * @param table
     * @param
     * @return
     */
    public Cursor findLikeKeyByStr(String table, String key, String str) {
        String sql = "select * from " + table + " where " + key + " like '%" + str + "%' limit 0,1000";
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;

    }

    public Cursor findByFrequency(String table, int familyID) {
        String sql = "SELECT * FROM " + table + " where FamilyID = '" + familyID + "' and IsDeleted = '0' order by RecordDate DESC limit 13";
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    public void deleteTable(String table) {
        String sql = "delete from " + table;
        db.execSQL(sql);
        VACUUM();
    }

    public Cursor findByTime(String table, String time) {
        String sql = "select * from " + table + " where " + " datetime(CreatedDate ) >   datetime ( '" + time + "') ";
        Cursor cursor = db.rawQuery(sql, null);
        return cursor;
    }

    public void insertFamily(Family family) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        String sql = "insert into " + SetData.TABLE_NAME_MEMBER_FAMILY + " values( null," + family.getFamilyId() + "," + family.getMemberId() + ",'"
                + family.getName() + "','" + family.getRealName() + "'," + family.getHeight() + "," + family.getWeight() + ",'" + family.getBirthday() + "',"
                + family.getGender() + "," + family.getWaistline() + "," + family.getHipline() + ",'" + family.getAvatar() + "','" + recordDate + "','"
                + recordDate + "','" + family.getPhone() + "')";
        System.out.println("sql=" + sql);
        db.execSQL(sql);
    }

    public void insertTemSleep(List<TemSleepDTO> dtos, String table) {

        db.beginTransaction(); // 手动设置开始事务
        try {
            // 批量处理操作
            sb = new StringBuilder();
            for (TemSleepDTO dto : dtos) {
                sb = new StringBuilder();
                sb.append("INSERT INTO ").append(table).append(" VALUES('").append(dto.getID()).append("','").append(dto.getFamilyID()).append("','")
                        .append(dto.getSleepRecordID()).append("','").append(dto.getMedix_X()).append("','").append(dto.getMedix_Y()).append("','")
                        .append(dto.getMedix_Z()).append("','").append(dto.getMedix_O()).append("','").append(dto.getTimeMillis()).append("','")
                        .append(dto.getRecordDate()).append("')");
                // Log.v(TAG, "insertTemSleep::" + sb.toString());
                db.execSQL(sb.toString());
            }
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交。
            // 在setTransactionSuccessful和endTransaction之间不进行任何数据库操作
        } catch (Exception e) {
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    public Cursor findAllTemSleep(String table, int FamilyID) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" where FamilyID = ").append(FamilyID)
                .append(" and RecordDate >'2014-11-25 07:30:00' ORDER BY RecordDate");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findAllTemSleep::" + sb.toString());
        return cursor;
    }

    /**
     * 睡眠监测时候插入数据 2分钟一条数据
     *
     * @param dto
     * @param table
     */
    public void insertMendaleSleep(Medix_Pub_Sync_SleepDetail dto, String table) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        sb = new StringBuilder();
        sb.append("Replace INTO ").append(table).append(" VALUES(null,").append(dto.getMemberID()).append(",'").append(dto.getSleepID()).append("','")
                .append(dto.getSleepDetailID()).append("','").append(dto.getSleepQuality()).append("' ,").append(dto.getSampleCount()).append(",")
                .append(dto.getActCount()).append(",").append(dto.getTurnCount()).append(",'").append(recordDate).append("','").append(dto.getUpdatedDate())
                .append("',").append(dto.getIsDeleted()).append(",").append(dto.getSnoreCount()).append(",").append(dto.getSnoreStopCount()).append(",").append(dto.getBreathRate()).append(",").append(dto.getSnoreDuration()).append(")");
        Log.v(TAG, "insertMendaleSleep::" + sb.toString());
        db.execSQL(sb.toString());
        db.releaseMemory();
    }

    /**
     * 单个插入睡眠监测数据详情：Medix_Pub_Sync_SleepDetail
     *
     * @param dto
     * @param table
     */
    public void InsertorUpdateMendaleSleep(Medix_Pub_Sync_SleepDetail dto, String table) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();
        sb.append("Replace INTO ").append(table).append(" VALUES(null,").append(dto.getMemberID()).append(",'").append(dto.getSleepID()).append("','")
                .append(dto.getSleepDetailID()).append("','").append(dto.getSleepQuality()).append("' ,").append(dto.getSampleCount()).append(",")
                .append(dto.getActCount()).append(",").append(dto.getTurnCount()).append(",'").append(recordDate).append("','").append(dto.getUpdatedDate())
                .append("',").append(dto.getIsDeleted()).append(",").append(dto.getSnoreCount()).append(",").append(dto.getSnoreStopCount()).append(",").append(dto.getBreathRate()).append(",").append(dto.getSnoreDuration()).append(")");
        Log.v(TAG, "insertMendaleSleep::" + sb.toString());
        db.execSQL(sb.toString());
    }

    /**
     * 批量插入睡眠监测数据详情：Medix_Pub_Sync_SleepDetail
     *
     * @param dtos
     * @param table
     */
    public void InsertorUpdateMendaleSleep2(List<Medix_Pub_Sync_SleepDetail> dtos, String table) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();
        db.beginTransaction(); // 手动设置开始事务
        try {
            // long start = System.currentTimeMillis();
            for (Medix_Pub_Sync_SleepDetail dto : dtos) {
                sb = new StringBuilder();
                sb.append("Replace INTO ").append(table).append(" VALUES(null,").append(dto.getMemberID()).append(",'").append(dto.getSleepID()).append("','")
                        .append(dto.getSleepDetailID()).append("','").append(dto.getSleepQuality()).append("' ,").append(dto.getSampleCount()).append(",")
                        .append(dto.getActCount()).append(",").append(dto.getTurnCount()).append(",'").append(dto.getCreatedDate()).append("','")
                        .append(recordDate).append("',").append(dto.getIsDeleted()).append(",").append(dto.getSnoreCount()).append(",").append(dto.getSnoreStopCount()).append(")");
                // Log.v(TAG, "insertMendaleSleep::" + sb.toString());
                db.execSQL(sb.toString());
            }
            // long end = System.currentTimeMillis();
            // Log.v("123", "插入：Medix_Pub_Sync_SleepDetail" + dtos.size() +
            // "条数据，耗时=" + (end - start));
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    /**
     * 批量插入睡眠监测数据详情：Medix_Pub_Sync_SleepDetail,使用SQLiteStatement方法插入
     *
     * @param dtos
     * @param table
     */
    public void InsertorUpdateMendaleSleep(List<Medix_Pub_Sync_SleepDetail> dtos, String table) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();

        sb.append("Replace INTO ").append(table)
                .append("(MemberID,SleepID,SleepDetailID,SleepQuality,SampleCount,ActCount,TurnCount,CreatedDate,UpdatedDate,IsDeleted,SnoreCount,SnoreStopCount,BreathRate,SnoreDuration)");
        sb.append(" VALUES( ?,?, ?, ?, ?,?, ?, ?, ?,?,?,?,?,?)");
        db.beginTransaction(); // 手动设置开始事务
        try {
             long start = System.currentTimeMillis();
            for (Medix_Pub_Sync_SleepDetail dto : dtos) {
                SQLiteStatement statement = db.compileStatement(sb.toString());
                // Log.v(TAG, "insertMendaleSleep::" + sb.toString());
                statement.bindLong(1, dto.getMemberID());
                statement.bindString(2, dto.getSleepID());
                statement.bindString(3, dto.getSleepDetailID());
                statement.bindDouble(4, 1);
                statement.bindLong(5, 1);
                statement.bindLong(6, dto.getActCount());
                statement.bindLong(7, dto.getTurnCount());
                statement.bindString(8, dto.getRecordDate());
                statement.bindString(9,dto.getRecordDate());
                statement.bindLong(10, dto.getIsDeleted());
                statement.bindLong(11,dto.getSnoreCount());
                statement.bindLong(12,dto.getSnoreStopCount());
                statement.bindLong(13,dto.getBreathRate());
                statement.bindLong(14,dto.getSnoreDuration());
                statement.execute();
            }
             long end = System.currentTimeMillis();
             Log.v("123", "插入：Medix_Pub_Sync_SleepDetail" + dtos.size() +
             "条数据，耗时=" + (end - start));
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }

    }

    public Cursor findDayMendaleSleep(String table, String SleepID, int memberID) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" where IsDeleted = 0 and memberID =").append(memberID).append(" and SleepID = '").append(SleepID)
                .append("' ORDER BY CreatedDate");
        // Log.v("test1", "findDayMendaleSleep::" + sb.toString());
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    public Cursor findAllTotal(String table, int MemberID, String SleepID) {
        StringBuilder sb;
        sb = new StringBuilder();
//         sb.append("select count(SleepID) as Count from(  select *  from  ").append(table).append(" where  MemberID = ").append(MemberID)
//         .append(" and IsDeleted = 0 GROUP BY SleepID )");
        sb.append("select count(SleepID) as Count from(  select *  from  ").append(table).append(" where  IsDeleted = 0 and  memberID = ").append(MemberID)
                .append(" and SleepID = '").append(SleepID).append("'  GROUP BY SleepID )");
        Cursor cursor = db.rawQuery(sb.toString(), null);
//         Log.v(TAG, "findTotal::" + sb.toString());
        return cursor;
    }

    public Cursor findMendaleSleepDetailByMemberId(String table, int memberID) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" where memberID =").append(memberID);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    /**
     * 通过SleepID查找细明
     * */
    public Cursor findMendaleSleepDetailBySleepId(String table, String SleepId) {
        StringBuilder builder = new StringBuilder();
        builder.append("select * from ").append(table).append(" where SleepID ='").append(SleepId).append("' ORDER BY CreatedDate");
       // builder.append("SELECT * FROM SleepSnore WHERE SleepID = '").append(SleepID).append("' ORDER BY RecordDate");

        return db.rawQuery(builder.toString(), null);
    }

    /**
     * 通过本地时间戳，查找Medix_Pub_Sync_SleepDetail需要同步数据
     *
     * @param table
     * @param memberID
     * @param
     * @return
     */
    public Cursor findMendaleSleepDetailByMemberId(String table, int memberID, String timestamp) {
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" where MemberID =").append(memberID).append(" and UpdatedDate=" + "'").append(timestamp).append("'");
         Log.v("test2", "findMendaleSleepDetailByMemberId::" + sb.toString());
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    @Override
    public synchronized void close() {
        super.close();
    }

    // public void InsertorUpdateMendaleSleepAll(String table,
    // Medix_Pub_Sync_Sleep dto) {
    // String recordDate = MyDateUtil.getDateFormatToString(null);
    // sb = new StringBuilder();
    // sb.append("Replace INTO ").append(table).append(" VALUES(null,'").append(dto.getSleepID())
    // .append("',").append(dto.getMemberID()).append(",").append(dto.getInBedTime()).append(",")
    // .append(dto.getSleepTime()).append(",").append(dto.getDeepSleepTime()).append(",0,0,")
    // .append(dto.getSleepScore()).append(",'").append(dto.getRemark()).append("',")
    // .append(dto.getWakeupState()).append(",'").append(dto.getStartDate()).append("','")
    // .append(dto.getEndDate()).append("','").append(recordDate).append("','").append(recordDate)
    // .append("',0)");
    // Log.v(TAG, "InsertorUpdateMendaleSleepAll::" + sb.toString());
    // db.execSQL(sb.toString());
    // }

    public void InsertorUpdateMendaleSleepAll(String table, Medix_Pub_Sync_Sleep dto) {
//        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();
        db.beginTransaction();
        Log.v(TAG, "lalala::" + dto.getSleepID());

        try{
            sb.append("Replace INTO ")
                    .append(table).append(" VALUES(null,'")//0
                    .append(dto.getSleepID()).append("',")//1
                    .append(dto.getInBedTime()).append(",")//2
                    .append(dto.getSleepTime()).append(",")//3
                    .append(dto.getDeepSleepTime()).append(",")//4
                    .append(dto.getSleepScore()).append(",'")//5
                    .append(dto.getRemark()).append("',")//6
                    .append(dto.getWakeupState()).append(",'")//7
                    .append(dto.getStartDate()).append("','")//8
                    .append(dto.getEndDate()).append("','")//9
                    .append(dto.getCreatedDate()).append("','")//10
                    .append(dto.getUpdatedDate()).append("',")//11
                    .append(dto.getIsDeleted()).append(",")//12
                    .append(dto.getQqHealth()).append(",'")//13
                    .append(dto.getDeviceName() == null ? android.os.Build.MODEL : dto.getDeviceName()).append("',")//14
                    .append(dto.getSourceType() == 0 ? 1 : dto.getSourceType()).append(",")//15
                    .append(dto.getSnoreTime()).append(",")//16
                    .append(dto.getSnoreCount()).append(",")//17
                    .append(dto.getBreathRate()).append(",")//18
                    .append(dto.getSnoreStopCount()).append(",")//19
                    .append(dto.getMemberID()).append(",")//20
                    .append(dto.getActCount()).append(",")//21
                    .append(dto.getTurnCount()).append(")");//22

            db.execSQL(sb.toString());
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
            Log.v(TAG, "InsertorUpdateMendaleSleepAll::" + sb.toString());
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            db.endTransaction(); // 处理完成
        }

        //
//        sb.append("Replace INTO ")
//                .append(table)
//                .append("(ID,SleepID,InBedTime,SleepTime,DeepSleepTime,SleepScore,Remark,WakeupState,StartDate,EndDate,CreatedDate,UpdatedDate,IsDeleted,qqHealth,DeviceName,SourceType,SnoreTime,SnoreCount,SnoreStopCount,BreathRate,MemberID,ActCount,TurnCount)");
//        sb.append(" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
//        try {
//            db.beginTransaction();
//            SQLiteStatement statement = db.compileStatement(sb.toString());
//            statement.bindNull(1);
//            statement.bindString(1, dto.getSleepID());
//            statement.bindLong(2, dto.getInBedTime());
//            statement.bindLong(3, dto.getSleepTime());
//            statement.bindLong(4, dto.getDeepSleepTime());
//            statement.bindLong(5, dto.getSleepScore());
//            statement.bindString(6, dto.getRemark());
//            statement.bindLong(7, dto.getWakeupState());
//            statement.bindString(8, dto.getStartDate());
//            statement.bindString(9, dto.getEndDate());
//            statement.bindString(10, dto.getCreatedDate() == null ? recordDate : dto.getCreatedDate());
//            statement.bindString(11, recordDate);
//            statement.bindLong(12, dto.getIsDeleted());
//            statement.bindLong(13, dto.getQqHealth());
//            statement.bindString(14, dto.getDeviceName() == null ? android.os.Build.MODEL : dto.getDeviceName());
//            statement.bindLong(15, dto.getSourceType() == 0 ? 1 : dto.getSourceType());
//            statement.bindLong(16, dto.getSnoreTime());
//            statement.bindLong(17, dto.getSnoreCount());
//            statement.bindLong(18, dto.getSnoreStopCount());
//            statement.bindLong(19, dto.getBreathRate());
//            statement.bindLong(20, dto.getMemberID());
//            statement.bindLong(21, dto.getActCount());
//            statement.bindLong(22, dto.getTurnCount());
//            statement.execute();
//            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
//        } catch (Exception e) {
//            // TODO: handle exception
//            e.printStackTrace();
//            Log.e(TAG, "InsertorUpdateMendaleSleepAll 插入：处理出现异常");
//        } finally {
//            db.endTransaction(); // 处理完成
//        }
        Log.e(TAG, "InsertorUpdateMendaleSleepAll 插入：处理完成"+"  "+sb.toString());

    }

    /**
     * 批量插入睡眠监测数据：Medix_Pub_Sync_Sleep
     *
     * @param table
     * @param dtos
     */
    public void InsertorUpdateMendaleSleepAll2(String table, List<Medix_Pub_Sync_Sleep> dtos) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();
        db.beginTransaction();
        try {
            // long start = System.currentTimeMillis();
            for (Medix_Pub_Sync_Sleep dto : dtos) {
                sb = new StringBuilder();
                sb.append("Replace INTO ").append(table).append(" VALUES(null,'").append(dto.getSleepID()).append("',").append(dto.getMemberID()).append(",")
                        .append(dto.getInBedTime()).append(",").append(dto.getSleepTime()).append(",").append(dto.getDeepSleepTime()).append(",")
                        .append(dto.getActCount()).append(",").append(dto.getTurnCount()).append(",").append(dto.getSleepScore()).append(",'")
                        .append(dto.getRemark()).append("',").append(dto.getWakeupState()).append(",'").append(dto.getStartDate()).append("','")
                        .append(dto.getEndDate()).append("','").append(dto.getCreatedDate()).append("','").append(recordDate).append("',")
                        .append(dto.getIsDeleted()).append(",'").append(dto.getDeviceName() == null ? android.os.Build.MODEL : dto.getDeviceName())
                        .append("',").append(dto.getSourceType() == 0 ? 1 : dto.getSourceType()).append(")");
                // Log.v(TAG, "InsertorUpdateMendaleSleepAll::" +
                // sb.toString());
                db.execSQL(sb.toString());
            }
            // long end = System.currentTimeMillis();
            // Log.v("123", "插入：Medix_Pub_Sync_Sleep" + dtos.size() + "条数据，耗时="
            // + (end - start));
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }

    }

    /**
     * 批量插入睡眠监测数据：Medix_Pub_Sync_Sleep,使用SQLiteStatement方法插入
     *
     * @param table
     * @param dtos
     */
    public void InsertorUpdateMendaleSleepAll(String table, List<Medix_Pub_Sync_Sleep> dtos) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();
        sb.append("Replace INTO ")
                .append(table)
                .append("(SleepID,InBedTime,SleepTime,DeepSleepTime,SleepScore,Remark,WakeupState,StartDate,EndDate,CreatedDate,UpdatedDate,IsDeleted,qqHealth,DeviceName,SourceType,SnoreTime,SnoreCount,BreathRate,SnoreStopCount,MemberID,ActCount,TurnCount)");
        sb.append(" VALUES(?,?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?, ?, ?,?,?,?,?,?,?,?)");
        db.beginTransaction();
        try {
             long start = System.currentTimeMillis();
            for (Medix_Pub_Sync_Sleep dto : dtos) {
                SQLiteStatement statement = db.compileStatement(sb.toString());
                 Log.v(TAG, "InsertorUpdateMendaleSleepAll::" +
                 sb.toString());
                 statement.bindNull(1);
                statement.bindString(1, dto.getSleepID());
                statement.bindLong(2, dto.getInBedTime());
                statement.bindLong(3, dto.getSleepTime());
                statement.bindLong(4, dto.getDeepSleepTime());
                statement.bindLong(5, dto.getSleepScore());
                statement.bindString(6, dto.getRemark());
                statement.bindLong(7, dto.getWakeupState());
                statement.bindString(8, dto.getStartDate());
                statement.bindString(9, dto.getEndDate());
                statement.bindString(10, dto.getCreatedDate());
                statement.bindString(11, dto.getUpdatedDate());
                statement.bindLong(12, dto.getIsDeleted());
                statement.bindLong(13, dto.getQqHealth());
                statement.bindString(14, dto.getDeviceName() == null ? android.os.Build.MODEL : dto.getDeviceName());
                statement.bindLong(15, dto.getSourceType() == 0 ? 1 : dto.getSourceType());
                statement.bindLong(16, dto.getSnoreTime());
                statement.bindLong(17, dto.getSnoreCount());
                statement.bindLong(18, dto.getBreathRate());
                statement.bindLong(19, dto.getSnoreStopCount());
                statement.bindLong(20, dto.getMemberID());
                statement.bindLong(21, dto.getActCount());
                statement.bindLong(22, dto.getTurnCount());
                statement.execute();
            }
             long end = System.currentTimeMillis();
             Log.v("123", "插入：Medix_Pub_Sync_Sleep" + dtos.size() + "条数据，耗时="
             + (end - start));
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }

    }

    public void updateWakeUpState(String table, int state, String ID, String RecordDate, int memberID) {
        sb = new StringBuilder();
        sb.append("UPDATE ").append(table).append(" SET WakeupState = '").append(state).append("',EndDate='").append(RecordDate).append("' where MemberID = ")
                .append(memberID).append(" and SleepID = '").append(ID).append("'");
        // Log.v(TAG, "updateWakeUpState::" + sb.toString());
        db.execSQL(sb.toString());
    }

    public Cursor findSleepAllID(String table, int postion, int memberID) {
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append("  where  MemberID = ").append(memberID)
                .append(" and IsDeleted = 0 ORDER BY CreatedDate desc LIMIT ").append(postion);
        // Log.v(TAG, "findSleepAllID::" + sb.toString());
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;

    }



    public Cursor findAllByTopicId(String table, int parentId) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append(" where parentId='").append(parentId).append("'");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findAllByTopicId::" + sb.toString());
        return cursor;
    }

    public Cursor findAllByTopicName(String table, String name) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append(" where Name='").append(name).append("'");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findAllByTopicName::" + sb.toString());
        return cursor;
    }

    public Cursor findAllById(String table, int parentId) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append(" where _id='").append(parentId).append("'");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findAllById::" + sb.toString());
        return cursor;
    }

    public Cursor findTotal(String table, int MemberID) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("select count(SleepID) as Count from(  select *  from  ").append(table).append(" where  MemberID = ").append(MemberID)
                .append(" and IsDeleted = 0 GROUP BY SleepID )");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findTotal::" + sb.toString());
        return cursor;
    }

    public Cursor AllInBed(String table, int MemberID) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("select sum(InBedTime) as Count  from  ").append(table).append(" where MemberID = ").append(MemberID).append("  and IsDeleted = 0");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "AllInBed::" + sb.toString());
        return cursor;
    }

    public Cursor AllTurnCount(String table, int MemberID, String sleepId) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("select sum(TurnCount) as Count  from  ").append(table).append(" where MemberID = ").append(MemberID).append("  and IsDeleted = 0 ")
                .append("  and SleepID ='").append(sleepId + "'");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        Log.v(TAG, "AllTurnCount::" + sb.toString());
        return cursor;
    }

    /**
     * 不是正常结束睡眠监测的 用于查询最后的插入时间
     *
     * @param table
     * @param memberID
     * @param iD
     * @return
     */
    public Cursor findLastDate(String table, int memberID, String iD) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("select * from  ").append(table).append(" where MemberID = ").append(memberID).append(" and IsDeleted =0 and SleepID ='").append(iD)
                .append("' ORDER BY CreatedDate DESC LIMIT 1");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findLastDate::" + sb.toString());
        return cursor;
    }

    public void updateTime(String table, String iD, int inBedTime, int sleepTime, int deepSleepTime, int sleepScore, int memberID) {
        Log.v(TAG, "updateTime::1");
        String updateTime = MyDateUtil.getDateFormatToString(null);
        sb = new StringBuilder();
        sb.append("UPDATE ").append(table).append(" set InBedTime = ").append(inBedTime).append(",SleepTime = ").append(sleepTime).append(",DeepSleepTime = ")
                .append(deepSleepTime).append(",SleepScore = ").append(sleepScore).append(",UpdatedDate = '").append(updateTime)
                .append("', qqHealth = 0 where SleepID = '").append(iD).append("' and MemberID = ").append(memberID).append(" and IsDeleted = 0 ");
        Log.v(TAG, "updateTime::" + sb.toString());
        db.execSQL(sb.toString());
    }

    public Cursor findDateTime(String table, int memberID) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from ( select strftime('%Y-%m', StartDate) as  StartDate  from ").append(table).append(" where  IsDeleted = 0 and MemberID = ")
                .append(memberID).append(" ) GROUP BY StartDate ORDER BY StartDate Desc ");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findDateTime::" + sb.toString());
        return cursor;
    }

    public Cursor findDateTimeASC(String table, int memberID) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("SELECT * from ( select strftime('%Y-%m', StartDate) as  StartDate  from ").append(table).append(" where  IsDeleted = 0 and MemberID = ")
                .append(memberID).append(" ) GROUP BY StartDate ORDER BY StartDate ASC ");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findDateTime::" + sb.toString());
        return cursor;
    }

    /**
     * 查找以StartDate 年月相同的数据，按月份查询 使用StartDate排序
     *
     * @param table
     * @param time
     * @param memberID
     * @return
     */
    public Cursor findMonthSleep(String table, String time, int memberID) {
        StringBuilder sb;
        sb = new StringBuilder();
        sb.append("select * from ").append(table).append(" where IsDeleted = 0 and MemberID = ").append(memberID).append(" and StartDate LIKE '").append(time)
                .append("%'").append(" ORDER BY StartDate Desc");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findMonthSleep::" + sb.toString());
        return cursor;
    }

    public Cursor findSleepByID(String table, String iD, int memberID) {
        sb = new StringBuilder();
        sb.append("SELECT * from  ").append(table).append(" where IsDeleted = 0 and MemberID = ").append(memberID).append(" and SleepID =  '").append(iD)
                .append("'");
        // Log.v(TAG, "findSleepByID::" + sb.toString());
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    // public Cursor findSleepByMemberId(String table, int memberID) {
    // sb = new StringBuilder();
    // sb.append("SELECT * from  ").append(table).append(" where MemberID = ").append(memberID);
    // Log.v(TAG, "findAllSleepByMemberId::" + sb.toString());
    // Cursor cursor = db.rawQuery(sb.toString(), null);
    // return cursor;
    // }

    /**
     * 通过本地时间戳，查找Medix_Pub_Sync_Sleep需要同步数据
     *
     * @param table
     * @param memberID
     * @param
     * @return
     */
    public Cursor findSleepByMemberId(String table, int memberID, String timestamp) {
        sb = new StringBuilder();
        sb.setLength(0);
        sb.append("SELECT * from ").append(table).append(" where MemberID = ").append(memberID).append(" and UpdatedDate=" + "'").append(timestamp).append("'");
         Log.v("test2", "findAllSleepByMemberId::" + sb.toString());
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    public Cursor findSleepByMemberId(String table, int memberID) {
        sb = new StringBuilder();
        sb.setLength(0);
        sb.append("SELECT * from  ").append(table).append(" where MemberID = ").append(memberID).append(" and IsDeleted = 0 order by StartDate asc");
         Log.v("test2", "findAllSleepByMemberId::" + sb.toString());
        Cursor cursor = db.rawQuery(sb.toString(), null);
        return cursor;
    }

    /**
     * 开启事务去删除一个睡眠ID的数据
     */
    public void updateSleepByID(String table, String table1, String ID, int memberID,String updateTime) {
        db.beginTransaction(); // 手动设置开始事务
        try {
            // 批量处理操作
            sb = new StringBuilder();
            sb.append("UPDATE ").append(table).append("  set  IsDeleted = 1 ,UpdatedDate = '").append(updateTime).append("' where  MemberID = ")
                    .append(memberID).append(" and SleepID = '").append(ID).append("'");
            db.execSQL(sb.toString());
//            sb = new StringBuilder();
//            sb.append("UPDATE ").append(table1).append("  set  IsDeleted = 1 ,UpdatedDate = '").append(updateTime).append("' where MemberID = ")
//                    .append(memberID).append(" and SleepID = '").append(ID).append("'");
//             Log.v(TAG, "updateSleepByID::" + sb.toString());
//            db.execSQL(sb.toString());
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交。
            // 在setTransactionSuccessful和endTransaction之间不进行任何数据库操作
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }
    }
    /**
     * 退出时删除用户信息
     */
    public void deleteSleepBymenberID(String table, int memberID) {
        db.beginTransaction(); // 手动设置开始事务
        try {
            // 批量处理操作
            sb = new StringBuilder();
            sb.append("UPDATE ").append(table).append("  set  IsDeleted = 1 ").append("where  MemberID = ").append(memberID);
            db.execSQL(sb.toString());

            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交。
            // 在setTransactionSuccessful和endTransaction之间不进行任何数据库操作
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    public Cursor findPostionByTime(String table, String time, int memberID) {
        StringBuilder sb = new StringBuilder();
        sb.append("select count(SleepID) as count from  ").append(table).append("  where  StartDate >'").append(time).append("' and MemberID = ")
                .append(memberID).append(" and IsDeleted = 0");
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findPostionByTime::" + sb.toString());
        return cursor;
    }

    /**
     * 根据ID查找省份
     */
    public Cursor findProvinceNameById(String table, int ID) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT * from ").append(table).append(" where ID = ").append(ID);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findProvinceNameById::" + sb.toString());
        return cursor;
    }

    /**
     * 根据ID查找城市
     */
    public Cursor findProvinceNameCityByID(String table, int provinceID, int cityID) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * from ").append(table).append(" where ProvinceID = ").append(provinceID).append(" and ID = ").append(cityID);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findProvinceNameCityByID::" + sb.toString());
        return cursor;
    }

    public Cursor findDistrictByID(String table, int provinceID, int cityID, int districtID) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * from ").append(table).append(" where ProvinceID = ").append(provinceID).append(" and CityID = ").append(cityID).append(" and ID =")
                .append(districtID);
        Cursor cursor = db.rawQuery(sb.toString(), null);
        // Log.v(TAG, "findDistrictByID::" + sb.toString());
        return cursor;
    }

    /**
     * 根据memberId查找sleepId
     *
     * @param table
     * @param MemberId
     * @return
     */
    public Cursor findSleepIDByMemberId(String table, int MemberId) {
        StringBuilder builder = new StringBuilder();
        builder.append("select SleepID from ").append(table).append(" where MemberID = ").append(MemberId);
        Cursor cursor = db.rawQuery(builder.toString(), null);
        return cursor;
    }

    /**
     * 根据日期，memberid查询数据
     *
     * @param table
     * @param date
     * @param MemberId
     * @return
     */
    public Cursor findByDateAndMemberId(String table, String date, int MemberId) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT * FROM Medix_Pub_Sync_Sleep2 WHERE MemberID = ").append(MemberId).append(" AND IsDeleted = 0 AND StartDate LIKE '").append(date)
                .append("%' ORDER BY StartDate ASC");
        return db.rawQuery(builder.toString(), null);
    }

    /**
     * 根据是否已上传QQ健康标志位获取数据
     *
     * @param table
     * @param MemberId
     * @return
     */
    public Cursor findByQQHealth(String table, int MemberId) {
        StringBuilder builder = new StringBuilder();
        builder.append("select * from ").append(table).append(" where MemberID = ").append(MemberId).append(" and qqHealth = 0");
        return db.rawQuery(builder.toString(), null);
    }

    /**
     * 根据sleepId查找sleepdetail
     *
     * @param table
     * @param MemberId
     * @param SleepID
     * @return
     */
    public Cursor findBySleepID(String table, int MemberId, String SleepID) {
        StringBuilder builder = new StringBuilder();
        builder.append("select * from ").append(table).append(" where MemberID = ").append(MemberId).append(" and SleepID = '").append(SleepID).append("'");
        return db.rawQuery(builder.toString(), null);
    }

    public void updateQQHealthBySleepID(String table, int memberId, String SleepID, int qqHealth) {
        // UPDATE Medix_Pub_Sync_Sleep SET qqHealth = 0 WHERE SleepID =
        // '141230035331546025'
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE ").append(table).append(" SET qqHealth = ").append(qqHealth).append(" WHERE SleepID = '").append(SleepID)
                .append("' and MemberID = ").append(memberId);
        db.execSQL(builder.toString());
    }

    public void updateQQHealth(String table, int memberId, int qqHealth) {
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE ").append(table).append(" SET qqHealth = ").append(qqHealth).append(" WHERE MemberID = ").append(memberId);
        db.execSQL(builder.toString());
    }

	/*-------鼾声--------*/

    /**
     * 插入到SleepSnore表中
     *
     * @param memberID
     * @param entity
     */
    public void insertToSnore(int memberID, SnoreEntity entity) {
        try {
            String recordDate = MyDateUtil.getDateFormatToString(null);
            StringBuilder builder = new StringBuilder();
            builder.append("INSERT INTO SleepSnore VALUES(NULL,");// ID
            builder.append("'").append(entity.getSleepID()).append("',");// SleepID
            builder.append("'").append(entity.getSnoreID()).append("',");// SnoreID
            builder.append(memberID).append(",");// memberID
            builder.append("'").append(entity.getDecibels()).append("',");// db
            builder.append(entity.getSampleCount()).append(",");// sampleCount
            builder.append(entity.getSnoreCount()).append(",");// SnoreCount
            builder.append("'").append(entity.getRecordDate() == null ? recordDate : entity.getRecordDate()).append("',");// RecordDate
            builder.append("'").append(entity.getCreatedDate() == null ? recordDate : entity.getCreatedDate()).append("',");// CreatedDate
            builder.append("'").append(entity.getUpdateDate() == null ? recordDate : entity.getUpdateDate()).append("',");// UpdateDate
            builder.append("'").append(entity.getIsDeleted()).append("',");//isDeleted
            builder.append("'").append(entity.getSnoreStartTime()).append("',");// // SnoreStartTime
            builder.append(entity.getSnoreEndTime()).append(")");//SnoreEndTime

            Log.v("insertToSnore", "sql::" + builder.toString());
            db.execSQL(builder.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据sleepid查找，按recordDate顺序排列
     *
     * @param SleepID
     * @return
     */
    public Cursor findSnoreBySleepID(String SleepID) {
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT * FROM SleepSnore WHERE SleepID = '").append(SleepID).append("' ORDER BY RecordDate");
            return db.rawQuery(builder.toString(), null);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 根据memberID查找，过滤条件为Updatedate比时间戳大
     *
     * @param memberID
     * @return
     */
    public Cursor findSnoreByMemberID(int memberID, String timeStamp) {
        Cursor cursor = null;
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("SELECT * from SleepSnore where MemberID = ").append(memberID).append(" and UpdatedDate>" + "'").append(timeStamp).append("'");
            // test
            // builder.append("SELECT * from SleepSnore where MemberID = ").append(memberID);
            //
            // Log.v("test2", "findSnoreByMemberID::" + builder.toString());
            cursor = db.rawQuery(builder.toString(), null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cursor;
    }

    public void insertOrUpdate2Snore(List<SnoreEntity> list) {
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder sb = new StringBuilder();

        sb.append("REPLACE INTO SleepSnore(SleepID,SnoreID,MemberID,Decibels,SampleCount,SnoreCount,RecordDate,CreatedDate,UpdatedDate,isDeleted) ");
        sb.append("VALUES(?,?,?,?,?,?,?,?,?,?)");
        db.beginTransaction(); // 手动设置开始事务
        try {
            for (SnoreEntity entity : list) {
                SQLiteStatement statement = db.compileStatement(sb.toString());
                // Log.v(TAG, "insertOrUpdate2Snore::" + sb.toString());
                statement.bindString(1, entity.getSleepID());
                statement.bindString(2, entity.getSnoreID());
                statement.bindLong(3, entity.getMemberID());
                statement.bindString(4, entity.getDecibels());
                statement.bindLong(5, entity.getSampleCount());
                statement.bindLong(6, entity.getSnoreCount());
                statement.bindString(7, entity.getRecordDate() == null ? recordDate : entity.getRecordDate());
                statement.bindString(8, entity.getCreatedDate() == null ? recordDate : entity.getCreatedDate());
                statement.bindString(9, entity.getUpdateDate() == null ? recordDate : entity.getUpdateDate());
                statement.bindLong(10, entity.getIsDeleted());
                statement.bindString(11,entity.getSnoreStartTime());
                statement.bindString(12,entity.getSnoreEndTime());
                statement.execute();
            }
            db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            db.endTransaction(); // 处理完成
        }
    }

    public void updateStartTime2Sleep(String sleepID, String startTime) {
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE ").append(SetData.TABLE_NAME_MEDIX_PUB_SYNC_SLEEP).append(" SET StartDate = '").append(startTime).append("' WHERE SleepID = '")
                .append(sleepID).append("'");
        db.execSQL(builder.toString());
    }

    public Cursor findSleepIDAndStartDate(int memberID) {
        // SELECT SleepID,StartDate FROM Medix_Pub_Sync_Sleep WHERE IsDeleted =
        // 0 ORDER BY StartDate
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT SleepID,StartDate FROM Medix_Pub_Sync_Sleep2 WHERE MemberID = ").append(memberID).append(" AND IsDeleted = 0 ORDER BY StartDate");
        return db.rawQuery(builder.toString(), null);
    }

    public Cursor findDay4month(int memberID, String month) {
        // SELECT DISTINCT strftime('%d', StartDate) AS StartDate FROM
        // Medix_Pub_Sync_Sleep WHERE IsDeleted = 0 AND StartDate LIKE
        // '2015-07%' ORDER BY StartDate ASC
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT DISTINCT strftime('%d', StartDate) AS StartDate FROM Medix_Pub_Sync_Sleep2 WHERE MemberID = ").append(memberID)
                .append(" AND IsDeleted = 0 AND StartDate LIKE '").append(month).append("%' ORDER BY StartDate ASC");
//        Log.v(TAG, "findDay4month::" + builder.toString());
        return db.rawQuery(builder.toString(), null);
    }

    public Cursor findNewestSleepID(int memberID) {
        // SELECT SleepID FROM Medix_Pub_Sync_Sleep WHERE MemberID = 180999 AND
        // IsDeleted = 0 ORDER BY StartDate DESC LIMIT 1
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT SleepID FROM Medix_Pub_Sync_Sleep2 WHERE MemberID = ").append(memberID)
                .append(" AND IsDeleted = 0 ORDER BY StartDate DESC LIMIT 1");
        Log.v(TAG, "findNewestSleepID::" + builder.toString());
        return db.rawQuery(builder.toString(), null);
    }

    public Cursor findNewestSleepIDByDate(int memberID, String date) {
        // SELECT SleepID FROM Medix_Pub_Sync_Sleep WHERE MemberID = 180999 AND
        // IsDeleted = 0 AND StartDate LIKE '2015-07-19%' ORDER BY StartDate
        // DESC LIMIT 1
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT SleepID FROM Medix_Pub_Sync_Sleep2 WHERE MemberID = ").append(memberID).append(" AND IsDeleted = 0 AND StartDate LIKE '")
                .append(date).append("%' ORDER BY StartDate DESC LIMIT 1");
        Log.v(TAG, "findNewestSleepIDByDate::" + builder.toString());
        return db.rawQuery(builder.toString(), null);
    }

    public void updateTurnCount(String id, int memberID, int turnCount) {
        // UPDATE Medix_Pub_Sync_Sleep SET TurnCount = 250 WHERE SleepID =
        // '150820101018032137'
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE Medix_Pub_Sync_Sleep2 SET TurnCount = ").append(turnCount).append(", UpdatedDate = '").append(recordDate)
                .append("' WHERE SleepID = '").append(id).append("' AND MemberID = ").append(memberID);
        Log.v(TAG, "updateTurnCount::" + builder.toString());
        db.execSQL(builder.toString());
    }

    public void updateSnore(String sleepID, int memberID, int snoreTime, int snoreCount, int snoreFrequency, int snore2AllActCount, int snore2AllTurnCount) {
        // UPDATE Medix_Pub_Sync_Sleep SET ActCount = 111 , SnoreCount = 222 ,
        // BreathRate = 333 WHERE SleepID = '123' AND MemberID = 345
        String recordDate = MyDateUtil.getDateFormatToString(null);
        StringBuilder builder = new StringBuilder();
        // 注意，用ActCount来记录snoreTime
        builder.append("UPDATE Medix_Pub_Sync_Sleep2 SET ActCount = ").append(snore2AllActCount).append(" , SnoreCount = ").append(snoreCount)
                .append(" , BreathRate = ").append(snoreFrequency).append(" , TurnCount = ").append(snore2AllTurnCount).append(" , SnoreTime = ")
                .append(snoreTime).append(", UpdatedDate = '").append(recordDate).append("' WHERE SleepID = '").append(sleepID).append("' AND MemberID = ")
                .append(memberID);
        Log.v(TAG, "updateSnore::" + builder.toString());
        db.execSQL(builder.toString());
    }

    /**
     * sqlite的VACUUM命令，用于删除数据后整理数据库文件，可使数据库文件大小减少
     */
    private void VACUUM() {
        db.execSQL("VACUUM");
    }
}
